import os
import logging
import pandas as pd
import oracledb
from fastapi import FastAPI, File, UploadFile, Response
from fastapi.middleware.cors import CORSMiddleware
import uvicorn
import cachetools
from fastapi.responses import FileResponse

app = FastAPI()
origins = ["*"]  # 允许的来源，可以是具体的域名列表，例如 ["http://localhost", "https://example.com"]
methods = ["GET", "POST", "PUT", "DELETE"]  # 允许的 HTTP 方法
allow_credentials = True  # 是否允许发送身份验证信息（cookies）到服务器

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=allow_credentials,
    allow_methods=methods,
    allow_headers=["*"],
)

log_file = 'download_log.txt'
logging.basicConfig(filename=log_file, level=logging.INFO, format='%(asctime)s - %(message)s', encoding='utf-8',
                    datefmt='%Y-%m-%d %H:%M:%S')


def fetch_datas_two():
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    connection = oracledb.connect(user='dyyy', password='cpt123', dsn=dsn)
    cursor = connection.cursor()
    query = """
        SELECT ID,
           BRAND_NAME,
           日期,
           品牌,
           产品ID,
           品类,
           商品,
           蝉妈妈商品链接,
           抖音商品链接,
           商品图片,
           商品分类,
           小店
    FROM 缺失数据产品表
    WHERE ROWNUM <= 2
        """
    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    return rows


def fetch_datas_all_all():
    '''
    删除缺失并添加数据源
    :return:
    '''
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    connection = oracledb.connect(user='dyyy', password='cpt123', dsn=dsn)
    cursor = connection.cursor()
    query = """
        SELECT ID,
           BRAND_NAME,
           日期,
           品牌,
           产品ID,
           品类,
           商品,
           蝉妈妈商品链接,
           抖音商品链接,
           商品图片,
           商品分类,
           小店,
           价格,
           销量,
           销售额,
           关联达人,
           关联视频,
           关联直播
    FROM 缺失数据产品表
        """
    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    return rows


def fetch_all_datas():
    '''
    为了下载填补缺失值用
    :return:
    '''
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    connection = oracledb.connect(user='dyyy', password='cpt123', dsn=dsn)
    cursor = connection.cursor()
    query = """
        SELECT ID,
           BRAND_NAME,
           日期,
           品牌,
           产品ID,
           品类,
           商品,
           蝉妈妈商品链接,
           抖音商品链接,
           商品图片,
           商品分类,
           小店
        FROM 缺失数据产品表
        """
    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    connection.close()
    return rows


def insert_product_match(brand: str, category: str, product_id: str):
    '''
    向产品ID匹配品牌品类表中插入一条数据

    :param brand: 品牌名称
    :param category: 产品类别
    :param product_id: 产品ID
    '''
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')

    try:
        # 使用上下文管理器自动管理连接和游标
        with oracledb.connect(user='dyyy', password='cpt123', dsn=dsn) as connection:
            with connection.cursor() as cursor:
                # 创建插入数据的 SQL 语句
                insert_query = """
                    INSERT INTO 产品ID匹配品牌品类表 (品牌, 品类, 产品ID)
                    VALUES (:brand, :category, :product_id)
                """

                # 执行插入操作
                cursor.execute(insert_query, brand=brand, category=category, product_id=product_id)
                logging.info(f'{product_id}执行完毕')
                # 提交事务
                connection.commit()

    except oracledb.DatabaseError as e:
        # 捕获数据库错误并输出错误信息
        logging.info(f"{product_id}数据库错误: {e}")
    except Exception as e:
        # 捕获其它异常并输出错误信息
        logging.info(f"发生异常: {e}")


# 缓存有效期设为一天，单位为秒
CACHE_EXPIRATION = 60 * 60  # 3600 秒
# 创建一个缓存对象，使用 TTL 缓存策略
cache = cachetools.TTLCache(maxsize=1, ttl=CACHE_EXPIRATION)


def get_cached_data():
    # 如果缓存中有数据且未过期，直接返回缓存数据
    if 'cached_data' in cache:
        return cache['cached_data']
    else:
        # 否则从数据库获取最新数据并更新缓存
        data = fetch_datas_two()
        cache['cached_data'] = data
        return data


def get_cached_all_data():
    # 如果缓存中有数据且未过期，直接返回缓存数据
    if 'cached_all_data' in cache:
        return cache['cached_all_data']
    else:
        # 否则从数据库获取最新数据并更新缓存
        data = fetch_all_datas()
        cache['cached_all_data'] = data
        return data


def query_product_ID_from_marry_product_table(product_ID):
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')

    # 使用 with 语句管理连接和游标
    with oracledb.connect(user='dyyy', password='cpt123', dsn=dsn) as connection:
        with connection.cursor() as cursor:
            # 使用参数化查询，避免 SQL 注入问题
            query = """
                    SELECT 品牌, 品类
                    FROM 产品ID匹配品牌品类表
                    WHERE 产品ID = :product_ID
                    """
            cursor.execute(query, {'product_ID': product_ID})

            # 获取查询结果
            result = cursor.fetchone()
            if result is not None:
                brand, category = result
                return brand, category
            else:
                # 如果没有找到产品ID，返回 None 或其他默认值
                return None, None


def delete_lose_product_by_product_ID(product_ID):
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    # 使用 with 语句管理连接和游标
    with oracledb.connect(user='dyyy', password='cpt123', dsn=dsn) as connection:
        with connection.cursor() as cursor:
            # 使用参数化查询，避免 SQL 注入问题
            delete_query = """
                            DELETE FROM 缺失数据产品表
                            WHERE 产品ID = :product_ID
                        """
            # 执行删除操作
            cursor.execute(delete_query, {'product_ID': product_ID})
            connection.commit()  # 提交更改以确保删除操作生效


def insert_product_to_data_source(row):
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    # 使用 with 语句管理连接和游标
    with oracledb.connect(user='dyyy', password='cpt123', dsn=dsn) as connection:
        with connection.cursor() as cursor:
            insert_sql = """
                                                        INSERT INTO 数据源 (
                                                            brand_name, 日期, 品牌,产品ID,  品类,商品, 蝉妈妈商品链接, 抖音商品链接, 商品图片, 商品分类, 小店, 价格, 销量, 销售额, 关联达人, 关联视频, 关联直播
                                                        ) VALUES (
                                                            :brand_name,:日期, :品牌,:产品ID,:品类,:商品, :蝉妈妈商品链接, :抖音商品链接, :商品图片, :商品分类, :小店, :价格, :销量, :销售额, :关联达人, :关联视频, :关联直播
                                                        )
                                                    """
            cursor.execute(insert_sql, {
                'brand_name': row[1],
                '日期': row[2],
                '品牌': row[3],
                '产品ID': row[4],
                '品类': row[5],
                '商品': row[6],
                '蝉妈妈商品链接': row[7],
                '抖音商品链接': row[8],
                '商品图片': row[9],
                '商品分类': row[10],
                '小店': row[11],
                '价格': row[12],
                '销量': row[13],
                '销售额': row[14],
                '关联达人': row[15],
                '关联视频': row[16],
                '关联直播': row[17]
            })
            # 提交事务
            connection.commit()
            logging.info('执行成功')


@app.get("/products")
async def get_products():
    rows = fetch_datas_two()
    json_results = []
    for row in rows:
        result_dict = {
            'ID': row[0],
            'BRAND_NAME': row[1],
            '日期': row[2],
            '品牌': row[3],
            '产品ID': row[4],
            '品类': row[5],
            '商品': row[6],
            '蝉妈妈商品链接': row[7],
            '抖音商品链接': row[8],
            '商品图片': row[9],
            '商品分类': row[10],
            '小店': row[11]
        }
        json_results.append(result_dict)

    return json_results


@app.post("/upload_excel")
async def upload_excel(file: UploadFile = File(...)):
    # 保存上传的文件到文件系统中
    file_path = "用户上传的文件.xlsx"  # 确保这个路径是有效的
    with open(file_path, "wb") as f:
        f.write(await file.read())
    df = pd.read_excel(file_path)
    for index, row in df.iterrows():
        brand = row['品牌']
        if pd.isna(brand):
            brand = row['BRAND_NAME']
        category = row['品类']
        product_ID = row['产品ID']
        insert_product_match(brand=brand, category=category, product_id=product_ID)
    logging.info('数据录入完毕,删除文件')
    os.remove(file_path)
    logging.info('文件已删除')
    # 更新缺失数据产品表和数据源
    rows_all = fetch_datas_all_all()
    for row_all in rows_all:
        row_all = list(row_all)
        product_ID = row_all[4]
        logging.info(f'开始处理{product_ID}')
        brand, category = query_product_ID_from_marry_product_table(product_ID)
        if brand is not None and category is not None:
            row_all[3] = brand
            row_all[5] = category
            # 往数据源中插入数据
            insert_product_to_data_source(row_all)
            logging.info(f'{product_ID}录入完毕')
            # 删除缺失数据产品表
            delete_lose_product_by_product_ID(product_ID)
            logging.info(f'{product_ID}删除完毕')
    # 返回一个无内容的响应
    return Response(status_code=204)


@app.get("/all_products")
async def get_all_products():
    # 从缓存中获取数据
    rows = get_cached_all_data()
    # 将数据转换为 DataFrame
    df = pd.DataFrame(rows, columns=['ID', 'BRAND_NAME', '日期', '品牌', '产品ID', '品类', '商品', '蝉妈妈商品链接',
                                     '抖音商品链接', '商品图片', '商品分类', '小店'])
    excel_path = "缺失数据产品表.xlsx"  # 确保这个路径是有效的
    df.to_excel(excel_path, index=False)
    # 生成 Excel 文件并返回文件响应
    return FileResponse(excel_path, filename="缺失数据产品表.xlsx")


if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)
